﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using ERP_RORZE_DLL.Product.DAO;
using ERP_RORZE_DLL.Product.database;
using System.Data.OleDb;
using ERP_RORZE_PERMISSION.Base;
using ERP_RORZE_PRODUCT.Product.BusOder;
using ERP_RORZE_SYSTEM;
using System.Data.Common;
using System.Data.SqlClient;
using ERP_RORZE_DLL.Product.Base;
using Utilities = ERP_RORZE_TOOL.Common.Utilities;

namespace ERP_RORZE_PRODUCT.Product.PartData
{
    public partial class ctrlPartList : DevExpress.XtraEditors.XtraUserControl
    {
        ClsPartDataDAO clspartDAO = new ClsPartDataDAO();
        DataTable dt;
        private LogUtils log;
        clsPart obj = new clsPart();

        public ctrlPartList()
        {
            InitializeComponent();
            log = new LogUtils();
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }
        void Re_load(object sender, EventArgs e)
        {
            dt = clspartDAO.GetAllPartID();
            dvgPartList.DataSource = dt;
            gridView1.BestFitColumns();
        }
        private void dvgPartList_Load(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void btnReload_Click_1(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn có thực sự muốn xóa dữ liệu!", "Message", MessageBoxButtons.OKCancel,
                                MessageBoxIcon.Question) == DialogResult.OK)
            {
                obj.ID = int.Parse(gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ID").ToString());
                if (clspartDAO.Delete(obj) > 0)
                {

                    //write Log
                    log.Table = LogConstant.Table.Product.PartList;
                    log.Task = LogConstant.Task.Delete;
                    log.Description = "PartID: " + gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PartID");
                    log.WriteLog();

                    MessageBox.Show("Đã xóa.");
                }
            }
            Re_load(sender, e);
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            frm_PartAdd frmAdd = new frm_PartAdd();
            frmAdd.Reload += new EventHandler(Re_load);
            frmAdd.Show();
        }

        private void btEdit_Click(object sender, EventArgs e)
        {
            try
            {
                frm_PartEdit frmEdit = new frm_PartEdit();
                frmEdit.Reload += new EventHandler(Re_load);
                frmEdit.PartNo = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PartNo").ToString();
                frmEdit.PartName = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PartName").ToString();
                frmEdit.SupplierID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "SupplierID").ToString();
                frmEdit.UpQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "UpQty").ToString();
                frmEdit.CustomerID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "CustomerID").ToString();
                frmEdit.Show();
            }
            catch
            {

            }
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            //FileDialog fileDialog = new OpenFileDialog();
            //fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            //if (fileDialog.ShowDialog() == DialogResult.OK)
            //{
            //    OleDbConnection connection = new OleDbConnection();
            //    try
            //    {
            //        string path = fileDialog.FileName;
            //        INISettings iniSettings = new INISettings();
            //        string connectString = iniSettings.SQLConnectionString;
            //        // Connection String to Excel Workbook
            //        string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

            //        connection.ConnectionString = excelConnectionString;
            //        OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
            //        connection.Open();
            //        // Create DbDataReader to Data Worksheet
            //        DbDataReader dr = command.ExecuteReader();

            //        // Bulk Copy to SQL Server 
            //        SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
            //        bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_Part]";

            //        SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("PartNo", "PartNo");
            //        bulkInsert.ColumnMappings.Add(date);

            //        SqlBulkCopyColumnMapping toolId = new SqlBulkCopyColumnMapping("PartName", "PartName");
            //        bulkInsert.ColumnMappings.Add(toolId);

            //        SqlBulkCopyColumnMapping makerId = new SqlBulkCopyColumnMapping("CustomerID", "CustomerID");
            //        bulkInsert.ColumnMappings.Add(makerId);

            //        SqlBulkCopyColumnMapping SupplierID = new SqlBulkCopyColumnMapping("SupplierID", "SupplierID");
            //        bulkInsert.ColumnMappings.Add(SupplierID);

            //        SqlBulkCopyColumnMapping UpQty = new SqlBulkCopyColumnMapping("UpQty", "UpQty");
            //        bulkInsert.ColumnMappings.Add(UpQty);

            //        bulkInsert.WriteToServer(dr);

            //        connection.Close();
            //        Re_load(sender, e);
            //    }
            //    catch (Exception ex)
            //    {
            //        MessageBox.Show(ex.Message);
            //        connection.Close();
            //    }
            //}

            ImportData();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            btDelete_Click(sender, e);
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            btEdit_Click(sender, e);
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            btnAddNew_Click(sender, e);
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            clsConstantsFuction.ExportToExcel(gridView1, 1, true);
        }

        private void ImportData()
        {
            INISettings mySetting = new INISettings();
            SqlConnection myConnection = new SqlConnection(mySetting.SQLConnectionString);

            int countOk = 0;
            int countDup = 0;
            int countErr = 0;

            List<Utilities.ListItem> listDup = new List<Utilities.ListItem>();
            List<Utilities.ListItem> listErr = new List<Utilities.ListItem>();
            FileDialog importFile = new OpenFileDialog();
            importFile.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (importFile.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    Control parent = progressPanel1.Parent;
                    progressPanel1.Location =
                        new Point(parent.Bounds.X + parent.Bounds.Width / 2 - progressPanel1.Width / 2,
                                  parent.Bounds.Y + parent.Bounds.Height / 2 - progressPanel1.Height / 2);
                    progressPanel1.Visible = true;
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                   importFile.FileName +
                                                   ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd =
                        new OleDbCommand("Select [PartNo],[PartName],[CustomerID],[SupplierID] ,[UpQty] from [Sheet1$]",
                                         excelConnection);
                    excelConnection.Open();
                    OleDbDataReader dReader;
                    dReader = cmd.ExecuteReader();
                    int i = 0;
                    int line = 1;

                    string PartNo = string.Empty;
                    string PartName = string.Empty;
                    string CustomerID = string.Empty;
                    string SupplierID = string.Empty;
                    string UpQty = string.Empty;

                    myConnection.Open();
                    while (dReader.Read())
                    {
                        bool exist = false;
                        line++;

                        //check null
                        try
                        {
                            if (dReader["PartNo"].ToString().Trim().Equals(string.Empty)
                                && dReader["PartName"].ToString().Trim().Equals(string.Empty)
                                && dReader["CustomerID"].ToString().Trim().Equals(string.Empty)
                                && dReader["SupplierID"].ToString().Trim().Equals(string.Empty)
                                && dReader["UpQty"].ToString().Trim().Equals(string.Empty)
                                )
                            {
                                countErr++;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartNo"].ToString(), "Null line"));
                                exist = true;
                            }
                        }
                        catch (Exception e)
                        {
                            countErr++;
                            listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartNo"].ToString(), e.Message));
                            exist = true;
                        }

                        //check valid data
                        if (!exist)
                        {
                            try
                            {
                                PartNo = (dReader["PartNo"].ToString().Trim().Equals(string.Empty))
                                             ? "null"
                                             : "N'" + dReader["PartNo"].ToString().Trim() + "'";
                                PartName = (dReader["PartName"].ToString().Trim().Equals(string.Empty))
                                                ? "null"
                                                : "N'" + dReader["PartName"].ToString().Trim() + "'";
                                CustomerID = (dReader["CustomerID"].ToString().Trim().Equals(string.Empty))
                                               ? "null"
                                               : "N'" + dReader["CustomerID"].ToString().Trim() + "'";
                                SupplierID = (dReader["SupplierID"].ToString().Trim().Equals(string.Empty))
                                                 ? "null"
                                                 : "'" + dReader["SupplierID"].ToString().Trim() + "'";
                                UpQty = (dReader["UpQty"].ToString().Trim().Equals(string.Empty))
                                            ? "null"
                                            : dReader["UpQty"].ToString().Trim();

                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(),
                                                                   dReader["PartNo"].ToString(),
                                                                   ex.Message));
                            }
                        }

                        //check duplicate
                        if (!exist)
                        {
                            try
                            {
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "SELECT * FROM [242_Part] WHERE [PartNo]=N'" +
                                                      dReader["PartNo"].ToString() + "'";
                                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                                if (dataReader.Read())
                                {

                                    exist = true;
                                    countDup++;
                                    listDup.Add(new Utilities.ListItem(line.ToString(), dReader["PartNo"].ToString(), "Duplicate"));
                                }
                                dataReader.Close();
                                cmd_sql.Dispose();
                            }
                            catch (Exception e)
                            {
                                exist = true;
                                listDup.Add(new Utilities.ListItem(line.ToString(), dReader["PartNo"].ToString(), e.Message));
                            }
                        }

                        //Insert to database
                        if (!exist)
                        {
                            try
                            {
                                string sql = "INSERT INTO [242_Part] ("
                                         + "[PartNo],[PartName],[CustomerID],[SupplierID] ,[UpQty] )"
                                         + " VALUES (" + PartNo + ", " + PartName + ", " + CustomerID + ","
                                         + SupplierID + "," + UpQty + ")";
                                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);

                                cmd_sql.ExecuteNonQuery();
                                cmd_sql.Dispose();
                                countOk++;

                                //write Log
                                log.Table = LogConstant.Table.Product.PartList;
                                log.Task = LogConstant.Task.Import;
                                log.Description = "PartNo: " + PartNo;
                                log.WriteLog();

                            }
                            catch (Exception ex)
                            {
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartNo"].ToString(), ex.Message));
                            }

                        }
                    }
                    myConnection.Close();
                    progressPanel1.Visible = false;
                    excelConnection.Close();
                    //MessageBox.Show(i + " records have been imported. \n" + countDup.ToString() + " record(s) duplicated (Lines: " + lineDup + ")\nLines have error OptionID: " + lineNo);
                    //LoadData();
                    Frm_ImportReport report = new Frm_ImportReport(countOk, listDup, listErr, "PartList", "PartNo");
                    report.StartPosition = FormStartPosition.CenterScreen;
                    report.Show();
                    report.Activate();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    progressPanel1.Visible = false;
                    MessageBox.Show(ex.Message);
                }

            }
        }

    }
}
